import paramiko
import psycopg2

# get province 
def get_province_code(province):
    con = psycopg2.connect(
        database="sentinel2",
        user="postgres",
        password="12345",
        host="192.168.2.172",
        port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT qhdm
    FROM china_sy
    WHERE qhmc = '{province}' 
    """
    cur.execute(sql)
    con.commit()
    code=[record[0] for record in cur.fetchall()][0]
    cur.close()
    con.close()
    return code

# get sentinel2 tile list
def get_province_tiles(province):
	con = psycopg2.connect(
	database="sentinel2",
	user="postgres",
	password="12345",
	host="192.168.2.172",
	port="5432"
	)
	cur = con.cursor()
	sql = f"""
	SELECT "中国有效哨兵格网分布wgs84".mgrs
	FROM china_sy
	JOIN "中国有效哨兵格网分布wgs84" 
	on st_intersects(china_sy.geom,"中国有效哨兵格网分布wgs84".geom)
	WHERE  china_sy.qhmc = '{province}' 
	"""
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_city_tiles(province, city):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_dsy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_dsy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_dsy.qhmc = '{city}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_sy
        WHERE  china_sy.qhmc = '{province}' 
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

# get sentinel2 tile list
def get_county_tiles(province, city, county):
    con = psycopg2.connect(
    database="sentinel2",
    user="postgres",
    password="12345",
    host="192.168.2.172",
    port="5432"
    )
    cur = con.cursor()
    sql = f"""
    SELECT "中国有效哨兵格网分布wgs84".mgrs
    FROM china_xy
    JOIN "中国有效哨兵格网分布wgs84" 
    on st_intersects(china_xy.geom,"中国有效哨兵格网分布wgs84".geom)
    WHERE  china_xy.qhmc = '{county}' 
    AND sjxzqdm in (
        SELECT qhdm
        FROM china_dsy
        WHERE qhmc = '{city}'
        AND sjxzqdm in (
            SELECT qhdm
            FROM china_sy
            WHERE china_sy.qhmc = '{province}'
        )  
    )
    """
    cur.execute(sql)
    con.commit()
    tiles=[record[0] for record in cur.fetchall()]
    tiles = [tile.upper() for tile in tiles]
    cur.close()
    con.close()
    return tiles

def ssh_client():

    province = input("请输入省份(必选,例如：河南省): ")
    while province =="":
        province = input("请输入省份(必选,例如：河南省): ")
    city = input("请输入地市(可选,例如：焦作市)：")
    if city == "":
        tiles = get_province_tiles(province)
    else:
        county = input("请输入县名(可选,例如：武陟县)：")
        if county == "":
            tiles = get_city_tiles(province, city)
        else:
            tiles = get_county_tiles(province, city, county)
    tiles = "-".join(tiles)
    
    year = input("请输入年份（必选, 例如：2022）：")
    while not year:
        year = input("请输入年份（必选, 例如：2022）：")
    month = input("请输入月份（必选, 例如：3）：")
    while not month:
        month = input("请输入月份（必选, 例如：3）：")

    maxcloud = input("请输入最大含云量（默认100，例如：80）：")
    maxcloud = 100 if maxcloud == "" else int(maxcloud)

    bands = input("请输入合成波段序列（默认：2-3-4-5-6-7-8-12）: ")
    bands = "2-3-4-5-6-7-8-12" if bands == "" else bands

    code = get_province_code(province)
    print(tiles)
    print(code)
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy)
    ssh.connect(
        hostname="192.168.2.172",
        port=22, 
        username="root", 
        password="DELL123dell")
    params = [
        "/home/冯洋洋/PythonEnvironment/RasterShapeEnv/bin/python",
        "/home/冯洋洋/PythonProjection/RasterShape/GetL1cRaster.py",
        f"--tiles {tiles}",
        f"--year {year}",
        f"--month {month}",
        f"--maxcloud {maxcloud}",
        f"--bands {bands}",
        f"--code {code}"
    ]
    command = " ".join(params)
    print(command)
    shell = ssh.invoke_shell()
    shell.settimeout(900)
    shell.send('export LANG=en_US.UTF-8 \n') #解决错误的关键，编码问题
    shell.send('export LANGUAGE=en \n')
    shell.send(command+'\n')
    buff = ''
    while not buff.endswith('# '):
        out = shell.recv(1024)
        buff += out.decode('utf8')
        print(out.decode('utf8'))     
    ssh.close()
    return

if __name__ == "__main__":
    ssh_client()